/*
 * DatabasePersonHandler.java
 */

package nxtgeninventorysystem.Model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import nxtgeninventorysystem.Data.Item;
import nxtgeninventorysystem.Data.ItemSearchOptions;
import nxtgeninventorysystem.Data.Person;
import nxtgeninventorysystem.Data.PersonSearchOptions;
import nxtgeninventorysystem.Data.Transaction;
import nxtgeninventorysystem.Data.TransactionSearchOptions;

/**
 * The DatabasePersonHandler class provides general database functionalities to
 * the Person entity including functionalities regarding some of its attributes.
 * @author Seraph Softworks
 */
public final class DatabasePersonHandler {

    public DatabasePersonHandler() {

    }

    public ArrayList <Item> getPersonItems(int person_id, ItemSearchOptions order, boolean ascending) {
        String asc = "";
        if(order.itemOrderSpecified()) {
            if(ascending)
                asc = "asc";
            else
                asc = "desc";
        }

        ArrayList<Item> collection = new ArrayList<Item>();
        ResultSet result = Database.executeQuery("select distinct item.* from item, person where item.person = person.id and person.id = " + person_id + order.getItemOrderString() + asc);
        try {
            while(result.next()) {
                Item item = new Item();
                item.setId(result.getInt("id"));
                item.setName(result.getString("name"));
                item.setPerson(Database.person.readPerson(result.getInt("person")));
                item.setDeleted(result.getBoolean("deleted"));
                item.setDescription(result.getString("description"));
                item.setType(result.getString("type"));
                collection.add(item);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseTransactionHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return collection;
    }

    public ArrayList <Transaction> getPersonTransactions(int person_id, TransactionSearchOptions order, boolean ascending) {
        String asc = "";
        if(order.transactionOrderSpecified()) {
            if(ascending)
                asc = "asc";
            else
                asc = "desc";
        }

        ArrayList<Transaction> collection = new ArrayList<Transaction>();
        ResultSet result = Database.executeQuery("select distinct transaction.* from transaction, person, (select transaction.id as 'transaction', COUNT(*) as 'items' from transaction, transactionitem where transactionitem.transaction = transaction.id group by transaction.id) counts where transaction.person = person.id and person.id = " + person_id + order.getTransactionOrderString() + asc);
        try {
            while(result.next()) {
                Transaction transaction = new Transaction();
                transaction.setId(result.getInt("id"));
                transaction.setType(result.getString("type"));
                transaction.setDate(result.getDate("date"));
                transaction.setPurpose(result.getString("purpose"));
                transaction.setPerson(Database.person.readPerson(result.getInt("person")));
                transaction.setItems(Database.transaction.getTransactionItems(result.getInt("id"), new ItemSearchOptions(), true));
                collection.add(transaction);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseTransactionHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return collection;
    }

    public int createPerson(Person person) {

        if(person == null)
            return 0;

        String query = "insert into person (name, contact_no, team) values(";
        query += "'" + person.getName().replace("\'","\\'").replace("\"", "\\\"") + "',";
        query += "'" + person.getContactNo().replace("\'","\\'").replace("\"", "\\\"") + "',";
        query += "'" + person.getTeam().replace("\'","\\'").replace("\"", "\\\"") + "')";

        if(! Database.executeUpdate(query)) {
            return 0;
        }
        else {
            ResultSet rst = Database.executeQuery("select LAST_INSERT_ID() as 'last'");
            try {
                if (rst.next()) {
                    return rst.getInt("last");
                }
            } catch (SQLException ex) {
                Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return 0;
    }

    public ArrayList<Person> searchPerson(Person values, PersonSearchOptions options, PersonSearchOptions order, boolean ascending) {
        try {
            String query = "select distinct * from person ";
            String _order = "";
            if (ascending) {
                _order = "asc";
            } else {
                _order = "desc";
            }

            if(options.personOptionsSpecified())
                query += "where ";

            ArrayList<String> conditions = values.getPersonSearchParametersQueryStrings(options);

            for (int i = 0; i < conditions.size(); i++) {
                query += conditions.get(i);
                if (i < conditions.size() - 1) {
                    query += " and ";
                }
            }

            if(order != null) {
                query += order.getPersonOrderString();
                if(order.personOrderSpecified())
                    query += _order;
            }

            ArrayList<Person> collection = new ArrayList<Person>();

            System.err.println(query);

            ResultSet result = Database.executeQuery(query);

            while(result.next()){
                Person person = new Person();
                person.setId(result.getInt("id"));
                person.setName(result.getString("name"));
                person.setContactNo(result.getString("contact_no"));
                person.setTeam(result.getString("team"));
                collection.add(person);
            }
            return collection;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Person readPerson(int person_id) {
        try {
            Person person = new Person();
            ResultSet result = Database.executeQuery("select distinct * from person where id = " + person_id);

            if(result.next()){
                person.setId(result.getInt("id"));
                person.setName(result.getString("name"));
                person.setContactNo(result.getString("contact_no"));
                person.setTeam(result.getString("team"));
            }
            else {
                return null;
            }

            return person;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public boolean updatePerson(Person values) {
        if(values == null)
            return false;

        String query = "update person set ";
        query += "name = " + "'" + values.getName().replace("\'","\\'").replace("\"", "\\\"") + "',";
        query += "contact_no = " + "'" + values.getContactNo().replace("\'","\\'").replace("\"", "\\\"") + "',";
        query += "team = " + "'" + values.getTeam().replace("\'","\\'").replace("\"", "\\\"") + "' ";
        query += "where id = " + "'" + values.getId() + "'";

        return Database.executeUpdate(query);
    }

    public boolean createTeam(String team_name) {
        return Database.executeUpdate("insert into team (team) values ('" + team_name.replace("'","").replace("\"", "") + "')");
    }

    public ArrayList<String> readTeams() {
        try {
            ResultSet result = Database.executeQuery("select distinct * from team");
            ArrayList<String> teams = new ArrayList<String> ();
            while (result.next()) {
                teams.add(result.getString("team"));
            }
            return teams;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public boolean updateTeam(String old_name, String new_name) {
        String newName = new_name.replace("\'","\\'").replace("\"", "\\\"");
        String oldName = old_name.replace("\'","\\'").replace("\"", "\\\"");

        return
            //create new team
            createTeam(newName)
                &&
            //change all persons with old team name to new team name
            Database.executeUpdate("update person set team = '" + newName + "' where team = '" + oldName + "'")
                &&
            //delete old team
            Database.executeUpdate("delete * from team where team = '" + oldName + "'");

    }

}
